package Test_Web;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.ServletException;
import javax.servlet.http.Cookie;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Servlet implementation class SearchPage
 */
public class SearchPage extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public SearchPage() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doPost(request,response);
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		String title = request.getParameter("Title");
		String year = request.getParameter("Year");
		String director = request.getParameter("Director");
		String firstName = request.getParameter("First");
		String lastName = request.getParameter("Last");
		Boolean once = false;
		
		// Carry out the SQL Select Statement
		try
		{
			try {
				Class.forName("com.mysql.jdbc.Driver").newInstance();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			Connection connect;
			connect = DriverManager.getConnection("jdbc:mysql:///moviedb","testuser","testpass");
			Statement tempStatement = connect.createStatement();
			// build sql command
			String command = "SELECT movies.id, movies.title, movies.year, movies.director" +
					" FROM movies, stars, stars_in_movies" +
					" WHERE movies.id = movie_id AND stars.id = star_id";
			once = true;
			//title not null
			if(!title.equals("")) //title not empty
			{
				if(once)
				{
					command+= " AND";
				}
				command+= " movies.title LIKE '%"+ title +"%'";
				once = true;
			}
			if(!year.equals(""))
			{
				if(once)
				{
					command+= " AND";
				}
				command+= " movies.year = "+ year;
				once = true;
			}
			if(!director.equals(""))
			{
				if(once)
				{
					command+= " AND";
				}
				command+=" movies.director like '%"+ director +"%'";
				once = true;
			}
			
			if(firstName.equals("") && lastName.equals("")) // both empty
			{
				//do nothing
			}
			else if (firstName.equals("")) // first empty
			{
				if(once)
				{
					command+= " AND";
				}
				command+= " last_name like '%" + lastName +"%'";
				once = true;
			}
			else if (lastName.equals("")) // last empty
			{
				if(once)
				{
					command+= " AND";
				}
				command+= " first_name like '%" + firstName +"%'";
				once = true;
			}
			else // none empty
			{
				if(once)
				{
					command+= " AND";
				}
				command+= " (first_name like '%"+ firstName +"%' AND last_name like '%" + lastName +"%')";
				once = true;
			}
			
			// Pass SQL Query as parameter to Movie List
			Cookie cookie = new Cookie("sqlQuery", command);
			cookie.setMaxAge(60*60);
			response.addCookie(cookie);
			// Pass SQL Query as parameter to Movie List
			String redirectURL = "MovieList";
			response.sendRedirect(redirectURL);
			
			

		}
		catch (SQLException e) {
			e.printStackTrace();
		}
	}

}
